Namespace PeriodProcessing



    ''' <summary>
    ''' Retrieves student satisfied requirements for a certification from the database
    ''' </summary>
    ''' <remarks></remarks>
    Public Class CertifSatisfiedReqsProvider
        Implements ICertificationSatisfiedRequirementProvider

        Public Overridable Function getStudentCertificationSatisfiedRequirement(ByVal period As Period, ByVal certificationType As CertificationType, _
                                                                    ByVal student As Student) _
                        As List(Of CertificationSatisfiedRequirement) Implements ICertificationSatisfiedRequirementProvider.getStudentCertificationSatisfiedRequirement

            Dim dbm As New CertificationSatisfiedRequirementDBMapper()

            'the following sql gets all CertificationSatisfiedRequirements 
            'that are not used for any certification
            Const STR_GET_ALL_PASSED_REQS As String = _
                "CertificationEarnedId is null and CertificationRequirementId in (" & _
                " select CertificationRequirementId from CertificationRequirement where CertificationTypeId={0}) " & _
                " and ClassMemberId in (select ClassMemberId from ClassMember where studentId={1})"

            Dim lst As List(Of CertificationSatisfiedRequirement) = dbm. _
                            findList(STR_GET_ALL_PASSED_REQS, _
                            certificationType.CertificationTypeId, student.StudentId)


            Const STR_GET_ALL_EXCEPTION_REQS As String = _
                "CertificationEarnedId is null and CertificationRequirementId in (" & _
                " select CertificationRequirementId from CertificationRequirement where CertificationTypeId={0}) " & _
                " and [StudentLessonExceptionUsageId] in (SELECT a.StudentLessonExceptionUsageId FROM StudentLessonExceptionUsage AS a INNER JOIN StudentLessonException AS b ON a.StudentLessonExceptionId = b.StudentLessonExceptionId WHERE b.StudentId={1})"

            lst.AddRange(dbm. _
                            findList(STR_GET_ALL_EXCEPTION_REQS, _
                            certificationType.CertificationTypeId, student.StudentId))

            Return lst
        End Function
    End Class
End Namespace
